#!/usr/bin/python
# -*- coding: utf-8 -*-
from odbc import ODBC
from util.logger import process_log
from util.tools import calculate_date_day
import pypyodbc

__author__ = 'xiwei'


class ACCESS(ODBC):

    def text_convert(self, table, tb_id, field, field_id, delta, fmt):
        max_value = self.read_index(tb_id, field_id)
        max_value = calculate_date_day(max_value, fmt, delta)
        if not max_value:
            return ''
        where = "where [%s] >= '%s'" % (field, max_value)
        return where

    def dump(self, database_name, table_name, fields_name, where, tb_id, return_cursor=False, breakpoint=-1):
        fields_with_table = map(lambda x: self.field_join(x[0], x[1]), [(table_name, field) for field in fields_name])
        fields_sql = ', '.join(fields_with_table)
        sql = "select %s from [%s] %s " % (fields_sql, table_name, where)
        cursor = self.cursor

        if return_cursor:
            process_log().info("Fetch data by sql: %s" % sql)
            cursor.execute(sql)
            return cursor

    def count(self, database, table, where):
        """
            Get the count of sync by giving info
        Args:
            database (str):
            table (str):
            where (str):

        Returns:
            sql_count (int):
        """
        sql = 'select count(*) from [%s] %s' % (table, where)
        process_log().info('Fetch rows count by sql statement: %s' % sql)
        self.cursor.execute(sql)
        res = self.cursor.fetchall()
        return str(res[0][0]) if res else '0'

    def max_value(self, database, table, index, where):
        sql = "select max([%s]) " \
              "from [%s] %s" % (index, table, where)
        process_log().info('Fetch max value by sql: %s' % sql)
        self.cursor.execute(sql)
        res = self.cursor.fetchall()
        return str(res[0][0]) if res else ''

    def field_join(self, table, field):
        # raw_type = self.field_dict.get(field, {}).get('raw_type', '').lower()
        return u'[%s]' % field

    def fetch_schema(self, database, table_name):
        row = self.cursor.columns(table=table_name)
        res = [(r[2], r[3], r[5], '') for r in row]
        return res

    def __init__(self, connect_info):
        ODBC.__init__(self, connect_info)
        driver = 'Microsoft Access Driver (*.Mdb)'
        connectionstr = "DRIVER={%s}; DBQ=%s;UID=%s;PWD=%s" % (
            driver, connect_info.get('database', ''), connect_info.get('uid', ''),
            connect_info.get('pwd', ''))
        self.cursor = pypyodbc.connect(connectionstr, unicode_results=True).cursor()
        self.database = connect_info.get('database')

    def fetch_databases(self):
        return [(self.database,)]

    def fetch_tables(self, database, view=True):
        """
        the result of tables():
            0:table_cat: The catalog name.
            1:table_schema: The schema name.
            2:table_name: The table name.
            3:table_type: One of TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY,
                LOCAL TEMPORARY, ALIAS, SYNONYM, or a data source-specific type name.
            4:remarks: A description of the table.

        Args:
            view: fetch view if true
            database:
        """
        row = self.cursor.tables()
        res = [(r[2], 'hack') for r in row if r[3] == 'TABLE']
        return res

    def fetch_fields(self, database, table):
        """
        the result of columns() is:
            0:table_cat
            1:table_schema
            2:table_name
            3:column_name
            4:data_type
            5:type_name
            6:column_size
            7:buffer_length
            8:decimal_digits
            9:num_prec_radix
            10:nullable
            11:remarks
            12:column_def
            13:sql_data_type
            14:sql_datetime_sub
            15:char_octet_length
            16:ordinal_position
            17:is_nullable: One of SQL_NULLABLE, SQL_NO_NULLS, SQL_NULLS_UNKNOWN.

        Args:
            table:
            database:
        """
        row = self.cursor.columns(table=table)
        res = [(r[3], self.trans_field_type(r[5]), r[17]) for r in row]
        return res

    def fetch_record_count(self, database, table):
        sql = '''SELECT COUNT(1) FROM [%s]''' % table
        self.cursor.execute(sql)
        return self.cursor.fetchall()


if __name__ == '__main__':
    pass
